When a database query or set of queries is created, there may be more to consider than simply what information will be returned by the query or queries. For example, system users and administrators may care not only that the correct data is returned by a query, but that the data is returned quickly and efficiently. Many variables can affect the speed of a query, such as, for example, the hardware and/or operating system being used. However, the structure of a query (e.g., the number of queries issued, the type and/or language of a query, the structure of data in a query, etc.) can also affect the query's speed and/or scalability. Additionally, data desired from a query is often located in many locations; and because of this, obtaining all the data can be slower than desired.
SQL (Structured Query Language) and MDX (Multidimensional Expressions) are two examples of languages used for interacting with a database. One difference between SQL and MDX is the ability of MDX to reference multiple dimensions. For example, MDX provides commands that are designed specifically to retrieve data as multidimensional data structures with almost any number of dimensions (each of these dimensions is referred to as an axis).
In SQL, the SELECT clause is used to define the column layout for a query, while the WHERE clause is used to define the row layout. However, in MDX the SELECT clause can be used to define several axis dimensions, while the WHERE clause is used to restrict multidimensional data to a specific dimension or member. In SQL, the WHERE clause is used to filter the data returned by a query. In MDX, the WHERE clause is used to provide a slice of the data returned by a query. The SQL query uses the WHERE clause to contain an arbitrary list of items that should (or should not) be returned in the result set. While a long list of conditions in the filter can narrow the scope of the data that is retrieved, there is no requirement that the elements in the clause will produce a clear and concise subset of data. In MDX, however, the concept of a slice means that each member in the WHERE clause identifies a distinct portion of data from a different dimension.
The process of creating an SQL query is also different than that of creating an MDX query. The creator of an SQL query visualizes and defines the structure of a two-dimensional rowset and writes a query on one or more tables to populate it. In contrast, the creator of an MDX query usually visualizes and defines the structure of a multidimensional dataset and writes a query on a single cube to populate it. This could result in a multidimensional dataset with any number of dimensions, for example, a one-dimensional dataset is possible. These differences are at least part of the reason that, for some queries, it is faster to use SQL than MDX, while for other queries, it is not even possible to use SQL.
Accordingly, there exists a need for systems and methods that generate fast, scalable, and efficient queries that consider, for example, query structure, query language, and/or the number of queries issued.